"""
读取Excel数据文件

excel类：你的需求是实现什么
1. 读取表头
2. 读取数据
初始化工作：加载excel，打开一个表单
"""
from openpyxl import load_workbook


class HandleExcel:
    # 传入Excel路径与工作簿名字
    def __init__(self, file_path, sheet_name):
        '''
        :param file_path: execl路径
        :param sheet_name: 工作表名称
        '''
        self.file_path = file_path
        self.sheet_name = sheet_name

    def read_all_datas(self):
        # 加载工作簿对象
        wb = load_workbook(self.file_path)
        sh = list(wb[self.sheet_name].rows)
        all_datas = []
        titles = [item.value for item in sh[1]]  # 获取表头

        for itme in sh[2:]:
            values = []
            for val in itme:
                values.append(val.value)
            res = dict(zip(titles, values))
            all_datas.append(res)
        return all_datas

    def write_data(self, row, column, value):  # Excel表写入数据
        '''
        :param row: 工作表横向坐标
        :param column: 工作表纵向坐标
        :param value: 写入内容
        :return:
        '''
        # 加载工作簿对象
        workbook = load_workbook(self.file_path)
        wb_li = workbook[self.sheet_name]
        # 写入数据
        wb_li.cell(row=row+2, column=column, value=value)
        workbook.save(self.file_path)

    def close_file(self):  # 关闭Excel操作
        wb = load_workbook(self.file_path)
        wb.close()


if __name__ == '__main__':

    # 获取文件路径
    import os

    file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "../TestDatas/api_test_data.xlsx")
    # print(file_path)
    exc = HandleExcel(file_path, "login")
    cases = exc.read_all_datas()
    # print(cases)
    exc.close_file()
    for case in cases:
        print(case)
